using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace ListaFacil
{
    class ListaFacilDB
    {
        private SqlConnection conexao;
        private string configuracaoConexao;
        private string usuario;
        private string senha;

        public string Senha
        {
            get { return senha; }
            set { senha = value; }
        }

        public string Usuario
        {
            get { return this.usuario; }
            set { usuario = value; }
        }
        

        public ListaFacilDB(string usuario, string senha)
        {
            this.usuario = usuario;
            this.senha = senha;
            this.configuracaoConexao = Properties.Settings.Default.StringConexao;
            this.configuracaoConexao += "User ID= " + this.usuario + "; PWD = " + this.senha;

        }

        ~ListaFacilDB()
        {
            try
            {
                if (this.conexao.State == ConnectionState.Open)
                    this.conexao.Close();
            }
            catch (Exception)
            {
            }
        }

        public bool Conecta()
        {
            if (this.conexao == null)
                this.conexao = new SqlConnection(this.configuracaoConexao);

            try
            {
                this.conexao.Open();
            }
            catch (Exception)
            {
                return false;
            }
            return true;            
        }

        public SqlConnection getConexao()
        {
            if (this.conexao == null)
                this.Conecta();

            return this.conexao;
        }

        public string ConfiguracaoConexao
        {
            get { return configuracaoConexao; }
        }

        public DataTable ExecuteQuery(string query)
        {
            if (this.conexao.State == ConnectionState.Closed)
                this.conexao.Open();

            SqlDataAdapter dataAdapter = new SqlDataAdapter();

            dataAdapter.SelectCommand = new SqlCommand(query, this.conexao);
            DataTable selectedDatatable = new DataTable();
            dataAdapter.Fill(selectedDatatable);

            return selectedDatatable;
        }

        public void ExecutaSql(string sql)
        {
            SqlCommand comando = new SqlCommand(sql, this.conexao);
            comando.Transaction = this.conexao.BeginTransaction();          

            try
            {
                comando.ExecuteNonQuery();
                comando.Transaction.Commit();
            }
            catch(Exception e )
            {
                comando.Transaction.Rollback();
                throw new Exception(e.Message);
            }
        }
    }
}
